mysql让count为0的记录也显示出来就是把条件写在count()内

如:

在mysql 下执行如下命令：select use_city,count(*) from data where os="Windows 2003 Std" group by use_city;得到的结果为：
+———–+———-+
| use_city | count(*) |
+———–+———-+
| Beijing | 2 |
| Chengdu | 2 |
| Chongqing | 1 |
| Dalian | 2 |
| Fuzhou | 2 |
| Guangzhou | 2 |
| Hangzhou | 2 |
| Nanjing | 2 |
| Shanghai | 21 |
| Shenyang | 5 |
| Wuhan | 1 |
+———–+———-+
11 rows in set (0.01 sec)

共有11条记录，问题是其中有一个城市“Tianjing”是没有记录符合的，怎么让他显示成
+———–+———-+
| use_city | count(*) |
+———–+———-+
| Beijing | 2 |
| Chengdu | 2 |
| Chongqing | 1 |
| Dalian | 2 |
| Fuzhou | 2 |
| Guangzhou | 2 |
| Hangzhou | 2 |
| Nanjing | 2 |
| Shanghai | 21 |
| Tianjing | 0 |
| Shenyang | 5 |
| Wuhan | 1 |
+———–+———-+

让它count为0的记录也显示出来，做法如下：

SELECT
use_city,
COUNT(CASE WHEN os=’Windows 2003 Std’ THEN 1 ELSE NULL END)
FROM
data
GROUP BY
use_city
真实如下:
select a.ID,PATH,a.WAREID,a.SELLTXT,a.LINKS,a.LEIXING,floor(ifnull(SE1,0)) as a1,a.SE2 as a2 ,
a.SE3 ,b.POS_PRICE as SE1, b.POS_PURCH as SE2,b.name,b.PRICE3, round(b.PRICE3/b.POS_PRICE*10,1) as zekou ,count(*) +floor(ifnull(SE1,0)) as f from ye_soye4 a left join ware b on a.WAREID=b.id left join billstock c on c.WAREID=b.id
left join billindex d on d.id=c.IndexID and date_format(d.BILLDATE, '%Y-%m-%d') >= a.begintime and d.LEIXING='团购订单'
where date_format(d.BILLDATE, '%Y-%m-%d') >= a.begintime and d.LEIXING='团购订单'

group by a.ID,PATH,a.WAREID,SELLTXT,LINKS,LEIXING,floor(ifnull(SE1,0)) , SE2 ,
SE3 ,b.POS_PRICE   , b.POS_PURCH   ,b.name,b.PRICE3, round(b.PRICE3/b.POS_PRICE*10,1)

可以写成:

select a.ID,PATH,a.WAREID,a.SELLTXT,a.LINKS,a.LEIXING,floor(ifnull(SE1,0)) as a1,a.SE2 as a2 ,
a.SE3 ,b.POS_PRICE as SE1, b.POS_PURCH as SE2,b.name,b.PRICE3, round(b.PRICE3/b.POS_PRICE*10,1) as zekou ,count(      CASE WHEN      date_format(d.BILLDATE, '%Y-%m-%d') >= a.begintime and d.LEIXING='团购订单' THEN 1 ELSE NULL END            )   as f from ye_soye4 a left join ware b on a.WAREID=b.id   left join billstock c on c.WAREID=b.id
left join billindex d on d.id=c.IndexID

group by a.ID,PATH,a.WAREID,SELLTXT,LINKS,LEIXING,floor(ifnull(SE1,0)) , SE2 ,
SE3 ,b.POS_PRICE , b.POS_PURCH ,b.name,b.PRICE3, round(b.PRICE3/b.POS_PRICE*10,1)